package com.wootion.dizhi.service.impl;

import cn.hutool.core.util.IdUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.wootion.dizhi.comon.TimeHandler;
import com.wootion.dizhi.dao.*;
import com.wootion.dizhi.entity.*;
import com.wootion.dizhi.exception.BusinessException;
import com.wootion.dizhi.response.Result;
import com.wootion.dizhi.runData.GlobalData;
import com.wootion.dizhi.service.DataCollectionService;
import com.wootion.dizhi.service.ExcelUtil;
import com.wootion.dizhi.vo.Condition;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 液位计
 */
@Slf4j
@Service
public class DataCollectionServiceImpl implements DataCollectionService {

    private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    private static final SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
    @Resource
    DeviceMapper deviceMapper;

    @Autowired
    private WarningMapper warningMapper;
    @Resource
    private ConfigurationDao configurationDao;
    @Resource
    private TemporaryDao temporaryDao;
    @Resource
    private ResourcesDao resourcesDao;
    @Resource
    private ConfigTypeDao configTypeDao;
    @Resource
    private CylinderDao cylinderDao;
    @Value("${file.upload}")
    private String exportDir;

    /**
     * 历史样品列表
     *
     * @param condition
     * @return
     */
    @Override
    public Result historicalList(Condition condition) {
        Page<Configuration> page = new Page<>(condition.getPageNo(), condition.getPageSize());
        LambdaQueryWrapper<Configuration> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(StringUtils.hasText(condition.getPeriod()), Configuration::getPeriod, condition.getPeriod());
        lambdaQueryWrapper.eq(StringUtils.hasText(condition.getConfigTypeId()), Configuration::getConfigTypeId, condition.getConfigTypeId());
        lambdaQueryWrapper.orderByDesc(Configuration::getStartTime).orderByDesc(Configuration::getEndTime);
        Page<Configuration> selectPage = configurationDao.selectPage(page, lambdaQueryWrapper);
        List<Configuration> records = selectPage.getRecords();
        //赋值类型
        if (records != null) {
            records.forEach(configuration -> {
                String configTypeId = configuration.getConfigTypeId();
                ConfigType configType = configTypeDao.selectById(configTypeId);
                if (configType != null) {
                    configuration.setType(configType.getConfigType());
                } else {
                    configuration.setType("");
                }
            });
            selectPage.setRecords(records);
        }
        return Result.success(selectPage);
    }

    /**
     * 历史样品-详情-基本信息
     *
     * @return
     */
    @Override
    public Result historicalBasicInfo(String id) {
        Configuration configuration = configurationDao.selectById(id);
        //去根据类型id查询类型名称
        String configTypeId = configuration.getConfigTypeId();
        if (StringUtils.hasText(configTypeId)) {
            ConfigType configType = configTypeDao.selectById(configTypeId);
            if (configType != null) {
                configuration.setType(configType.getConfigType());
            } else {
                configuration.setType("");
            }
        }
        return Result.success(configuration);
    }

    /**
     * 历史样品-详情-曲线图
     *
     * @return
     */
    @Override
    public Result historicalGraph(String id, String hour) {
        LambdaQueryWrapper<Resources> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Resources::getResId, id);
        lambdaQueryWrapper.orderByAsc(Resources::getDate)
                .orderByAsc(Resources::getTime);
        List<Resources> resources = resourcesDao.selectList(lambdaQueryWrapper);

        List<Resources> resourcesHour = new ArrayList<>();
        //如果是查询单位是全部，也就是单位是小时 hour = null
        if (!StringUtils.hasText(hour)) {
            if (!CollectionUtils.isEmpty(resources)) {
                for (int i = 0; i < resources.size(); i++) {
                    if (i % 60 == 0) {
                        resourcesHour.add(resources.get(i));
                    }
                }
                return Result.success(resourcesHour);
            } else {
                return Result.success();
            }
        } else {
            // 如果是查询是某一个小时的分钟，那么 hour=14 查询当前这个小时的所有数据
//            List<Resources> resourcesList = resources.stream()
//                    .filter(t -> {
//                        String time = t.getTime();
//                        return time.split(":")[0].contains(hour);
//                    }).collect(Collectors.toList());

            // 如果是查询是某一个小时的分钟，那么 hour=14 查询当前这个小时的所有数据
            //temporaryList2存储符合条件的元素
            int j = 0;
            List<Resources> resourcesList2 = new LinkedList<>();
            for (int i = 0; i < resources.size(); i++) {
                Resources resources1 = resources.get(i);
                if(hour.equals(resources1.getTime())){
                    //获取指定位置元素的索引
                    j = resources.indexOf(resources1);
                }
            }
            for (int i = j; i < resources.size(); i++) {
                if(resourcesList2.size()<60){
                    resourcesList2.add(resources.get(i));
                }
            }

            return Result.success(resourcesList2);
        }
    }

    /**
     * 历史样品-详情-原始数据
     *
     * @return
     */
    @Override
    public Result historicalRawData(Condition condition) {
        Page<Resources> page = new Page<>(condition.getPageNo(), condition.getPageSize());
        LambdaQueryWrapper<Resources> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Resources::getResId, condition.getId());
        lambdaQueryWrapper.orderByAsc(Resources::getDate).orderByAsc(Resources::getTime);
        Page<Resources> selectPage = resourcesDao.selectPage(page, lambdaQueryWrapper);
        return Result.success(selectPage);
    }

    /**
     * 历史样品-详情-原始数据
     *
     * @return
     */
    @Override
    public Result historicalExport(String id) {
        LambdaQueryWrapper<Resources> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Resources::getResId, id);
        lambdaQueryWrapper.orderByAsc(Resources::getDate).orderByAsc(Resources::getTime);
        List<Resources> resources = resourcesDao.selectList(lambdaQueryWrapper);
        //开始写excel
        // 创建一个97年-2003.xls工作簿(一个工作簿中可以创建多个工作表sheet)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //使用工作簿创建一个工作表
        HSSFSheet sheet = hssfWorkbook.createSheet("原始数据");
        HSSFRow row = sheet.createRow(0);//获取第一行
        //设置excel表头
        String[] headers = new String[]{"日期", "时间", "读数(ml)"};
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);//当前行的第几列
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);//获取头信息，这个对象会自动给你判空
            cell.setCellValue(text);//设置值
        }
        for (int i = 0; i < resources.size(); i++) {
            //创建当前行
            HSSFRow row1 = sheet.createRow(i + 1);//创建第一行(从第二行开始)
            //设置日期
            HSSFCell cell1 = row1.createCell(0);//当前行的第1列
            if (StringUtils.hasText(resources.get(i).getDate())) {
                cell1.setCellValue(resources.get(i).getDate());
            }
            //设置时间
            HSSFCell cell2 = row1.createCell(1);//当前行的第2列
            if (Objects.nonNull(resources.get(i).getTime())) {
                cell2.setCellValue(resources.get(i).getTime());
            }
            //设置读数
            HSSFCell cell3 = row1.createCell(2);//当前行的第3列
            if (Objects.nonNull(resources.get(i).getValue())) {
                cell3.setCellValue(resources.get(i).getValue());
            }
        }
        String filename = System.currentTimeMillis() + IdUtil.simpleUUID() + ".xls";
        OutputStream outputStream = null;
        try {
            File exportDirFile = new File(exportDir);
            log.info("exportDir={}", exportDir);
            log.info("exportDirFile={}", exportDirFile.getPath());
            if (!exportDirFile.exists()) {
                boolean mkdirs = exportDirFile.mkdirs();
                if (mkdirs) {
                    log.info("创建文件夹{}成功", exportDirFile);
                } else {
                    log.error("创建文件夹失败");
                }
            }
            String fileUrl = exportDirFile.getPath() + File.separator + filename;
            log.info("fileUrl={}", fileUrl);
            outputStream = Files.newOutputStream(Paths.get(fileUrl));
            hssfWorkbook.write(outputStream);
            //将所有的windows \ 换成 / 以免Linux不支持
            return Result.success(fileUrl.replace("\\", "/"));
        } catch (Exception exception) {
            exception.printStackTrace();
            return Result.error("IO error");
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();//关闭流
                }
                hssfWorkbook.close();//关闭流
            } catch (Exception exception) {
                exception.printStackTrace();
            }
        }
    }

    /**
     * 原始数据
     *
     * @param condition
     * @return
     */
    @Override
    public Result rawData(Condition condition) {
        //获取临时表中的数据
        String address = condition.getAddress();
        String experiment = condition.getExperiment();
        String addressExperiment = address + experiment;

        Page<Temporary> page = new Page<>(condition.getPageNo(), condition.getPageSize());
        LambdaQueryWrapper<Temporary> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Temporary::getAddressExperiment, addressExperiment);
        //按照时间排序
        lambdaQueryWrapper.orderByDesc(Temporary::getDate).orderByDesc(Temporary::getTime);
        Page<Temporary> temporaryPage = temporaryDao.selectPage(page, lambdaQueryWrapper);
        return Result.success(temporaryPage);
    }

    /**
     * 曲线图
     *
     * @return
     */
    @Override
    public Result graph(String address, String experiment) {
        String addressExperiment = address + experiment;
        LambdaQueryWrapper<Temporary> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Temporary::getAddressExperiment, addressExperiment);
        lambdaQueryWrapper.orderByAsc(Temporary::getDate)
                .orderByAsc(Temporary::getTime);
        List<Temporary> temporaries = temporaryDao.selectList(lambdaQueryWrapper);
        List<Temporary> temporariesHour = new ArrayList<>();
        if (!CollectionUtils.isEmpty(temporaries)) {
            for (int i = 0; i < temporaries.size(); i++) {
                if (i % 60 == 0) {
                    temporariesHour.add(temporaries.get(i));
                }
            }
            return Result.success(temporariesHour);
        } else {
            return Result.success();
        }
    }

    /**
     * 曲线图
     *
     * @return
     */
    @Override
    public Result graphHour(String address, String experiment, String hour) {
        String addressExperiment = address + experiment;
        LambdaQueryWrapper<Temporary> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Temporary::getAddressExperiment, addressExperiment);
        lambdaQueryWrapper.orderByAsc(Temporary::getDate)
                .orderByAsc(Temporary::getTime);
        List<Temporary> temporaries = temporaryDao.selectList(lambdaQueryWrapper);
        if(!CollectionUtils.isEmpty(temporaries)){
            // 如果是查询是某一个小时的分钟，那么 hour=14 查询当前这个小时的所有数据
            //temporaryList2存储符合条件的元素
            int j = 0;
            List<Temporary> temporaryList2 = new LinkedList<>();
            for (int i = 0; i < temporaries.size(); i++) {
                Temporary temporary = temporaries.get(i);
                if(hour.equals(temporary.getTime())){
                    //获取指定位置元素的索引
                    j = temporaries.indexOf(temporary);
                }
            }
            for (int i = j; i < temporaries.size(); i++) {
                if(temporaryList2.size()<60){
                    temporaryList2.add(temporaries.get(i));
                }
            }
            return Result.success(temporaryList2);
        }
        return Result.success();
    }

    /**
     * 新增配置类型
     *
     * @param configType
     * @return
     */
    @Override
    public Result insertConfigType(ConfigType configType) {
        configType.setCreateTime(new Date());
        int rows = configTypeDao.insert(configType);
        return rows > 0 ? Result.success() : Result.error("新增类型失败");
    }

    /**
     * 根据id查询配置类型
     *
     * @param id
     * @return
     */
    @Override
    public Result findByConfigTypeId(String id) {
        ConfigType configType = configTypeDao.selectById(id);
        return Result.success(configType);
    }

    /**
     * 根据id删除配置类型
     *
     * @param id
     * @return
     */
    @Override
    public Result deleteConfigTypeId(String id) {
        int row = configTypeDao.deleteById(id);
        return row > 0 ? Result.success() : Result.error("删除类型失败");
    }

    /**
     * 更新配置类型
     *
     * @param configType
     * @return
     */
    @Override
    public Result updateConfigType(ConfigType configType) {
        int row = configTypeDao.updateById(configType);
        return row > 0 ? Result.success() : Result.error("更新类型失败");
    }

    /**
     * 配置类型列表查询
     *
     * @param condition
     * @return
     */
    @Override
    public Result configTypeList(Condition condition) {
        Page<ConfigType> page = new Page<>(condition.getPageNo(), condition.getPageSize());
        LambdaQueryWrapper<ConfigType> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.orderByDesc(ConfigType::getCreateTime);
        Page<ConfigType> selectPage = configTypeDao.selectPage(page, lambdaQueryWrapper);
        return Result.success(selectPage);
    }

    /**
     * 配置类型下拉选项
     *
     * @param
     * @return
     */
    @Override
    public Result configTypeListOptions() {
        List<ConfigType> configTypes = configTypeDao.selectList(null);
        return Result.success(configTypes);
    }

    /**
     * 气象色谱仪列表
     *
     * @param
     * @return
     */
    @Override
    public Result atmosphereList(Condition condition) {
        Page<Configuration> page = new Page<>(condition.getPageNo(), condition.getPageSize());
        LambdaQueryWrapper<Configuration> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(StringUtils.hasText(condition.getPeriod()), Configuration::getPeriod, condition.getPeriod());
        lambdaQueryWrapper.eq(StringUtils.hasText(condition.getConfigTypeId()), Configuration::getConfigTypeId, condition.getConfigTypeId());
        lambdaQueryWrapper.orderByDesc(Configuration::getStartTime).orderByDesc(Configuration::getEndTime);
        Page<Configuration> selectPage = configurationDao.selectPage(page, lambdaQueryWrapper);
        List<Configuration> records = selectPage.getRecords();
        //赋值类型
        if (records != null) {
            records.forEach(configuration -> {
                String configTypeId = configuration.getConfigTypeId();
                ConfigType configType = configTypeDao.selectById(configTypeId);
                if (configType != null) {
                    configuration.setType(configType.getConfigType());
                } else {
                    configuration.setType("");
                }
            });
            selectPage.setRecords(records);
        }
        return Result.success(selectPage);
    }

    /**
     * 气象色谱仪-导入
     *
     * @param
     */
    @Override
    public Result atmosphereExport(MultipartFile multipartFile) throws BusinessException {
        //此集合用来存储读到上传上来的所有文件的数据
        List<Cylinder> cylinders = new ArrayList<>();
        List<String[]> dataList = new ArrayList<>();
        //获得原文件名
        String fileName = multipartFile.getOriginalFilename();
        //判断文件是否是excel文件
        assert fileName != null;
        if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
            throw new BusinessException(fileName + "不是excel文件");
        }
        try {
            //获取文件流
            Workbook workbook = WorkbookFactory.create(multipartFile.getInputStream());
            log.info("成功将上传的文件转为excel工作簿");
            //获取sheet的页数 sheet页数也是从1开始
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {
                //获取当前页的sheet
                Sheet sheet = workbook.getSheetAt(sheetNum);
                //来获取该 sheet 中包含的行数。如果行数为 0，则该 sheet 为空。
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                if (physicalNumberOfRows == 0) {
                    continue;
                }
                //获取表头(也就是第0行)这一行
                Row row0 = sheet.getRow(0);
                //根据表头行，得到其迭代器
                Iterator<Cell> cellIterator = row0.cellIterator();
                //根据表头行的迭代器遍历，拿到每一个表头字段
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    System.out.println(cell.getStringCellValue());
                }
                //获得当前sheet的第一个有数据的行号开始行号
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的最后一个有数据的行号结束行号
                int lastRowNum = sheet.getLastRowNum();
                //这里跳过第0行，因为第0行是标题，所以是firstRowNum+1开始获取
                for (int nowRowNum = firstRowNum + 1; nowRowNum <= lastRowNum; nowRowNum++) {
                    Row row = sheet.getRow(nowRowNum);
                    if (Objects.isNull(row)) {
                        continue;
                    }
//                    //获得一行中不为空的第一个单元格的列号，从0开始
//                    int firstCellNum = row.getFirstCellNum();
                    //获取不为空的的列个数。个数从1开始计算
                    int countCellNum = row.getPhysicalNumberOfCells();
                    //如果无量缸没有数据
                    if (countCellNum == 1) {
                        countCellNum = 2;
                    }
                    //此数组用来存储一行中每一个单元格中的数据，数组长度在初始化时，就是当前行不为空的列的个数
                    String[] cellData = new String[countCellNum];
                    for (int cellNum = 0; cellNum < 2; cellNum++) {
                        //获取当前行中的每一个单元格
                        Cell cell = row.getCell(cellNum);
                        if (cell == null) {
                            cellData[cellNum] = "";
                            continue;
                        }
                        //设置单元格类型
                        cell.setCellType(CellType.STRING);
                        String stringCellValue = cell.getStringCellValue();
                        if (NumberUtils.isCreatable(stringCellValue)) {
                            NumberFormat numberFormat = NumberFormat.getInstance();
                            //如果在格式化和分析数字时使用千分隔符，则为true.
                            numberFormat.setGroupingUsed(false);
                            //x设置小数点保留位数，这里保留10位小数
                            numberFormat.setMaximumFractionDigits(10);
                            //numberFormat.format()格式化小数
                            stringCellValue = numberFormat.format(Double.valueOf(stringCellValue));
                        }
                        cellData[cellNum] = (stringCellValue);
                        System.out.println(stringCellValue);
                    }
                    //将读取到的每一行的数据都装起来
                    dataList.add(cellData);
                }
            }
            //关闭流
            workbook.close();
        } catch (IOException e) {
            log.error("上传的文件不是excel,不能转换");
            e.printStackTrace();
        }
        log.info(JSON.toJSONString(dataList));
        //最后将读取到的文件数据，返回给前端
        for (String[] strings : dataList) {
            Cylinder cylinder = new Cylinder();
            cylinder.setDateTime(strings[0]);
            cylinder.setCylinderData(strings[1]);
            cylinders.add(cylinder);
        }
        //开始校验数据格式
        for (Cylinder cylinder : cylinders) {
            //成功 sort=1
            cylinder.setSort(1);
            //校验日期时间格式 年-月-日 时:分  yyyy-MM-dd HH:mm
            String dateTime = cylinder.getDateTime();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            try {
                Date parse = simpleDateFormat.parse(dateTime);
            } catch (ParseException e) {
                //失败 sort=0
                cylinder.setSort(0);
                e.printStackTrace();
            }
            //校验数据格式
            String cylinderData = cylinder.getCylinderData();
            if (!this.strData(cylinderData)) {
                //失败 sort=0
                cylinder.setSort(0);
            }
        }
        return Result.success(cylinders);
    }

    /**
     * 气象色谱仪-保存导入的数据
     *
     * @param
     * @return
     */
    @Override
    public Result atmosphereExportSave(String resId, List<Cylinder> cylinders) {
        //先删除和resId相关的无量缸数据
        LambdaQueryWrapper<Cylinder> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Cylinder::getResId, resId);
        int rows = cylinderDao.delete(lambdaQueryWrapper);
        log.info("先删除之前导入的无量缸数据,一共删除了{}条", rows);
        //编列集合，给所有新的数据赋值关联的resId
        cylinders = cylinders.stream()
                .peek(cylinder -> cylinder.setResId(resId))
                .collect(Collectors.toList());
        int sort = 1;
        for (Cylinder cylinder : cylinders) {
            cylinder.setSort(sort);
            cylinderDao.insert(cylinder);
            sort++;
        }
        //修改数量
        Configuration configuration = configurationDao.selectById(resId);
        configuration.setExportNumber(cylinders.size());
        configurationDao.updateById(configuration);
        return Result.success();
    }

    /**
     * 气象色谱仪-详情-原始数据分页列表
     *
     * @param
     * @return
     */
    @Override
    public Result atmosphereExportList(Condition condition) {
        String id = condition.getId();
        Page<Cylinder> page = new Page<>(condition.getPageNo(), condition.getPageSize());
        LambdaQueryWrapper<Cylinder> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Cylinder::getResId, id);
        //按照sort升序排序 1,2,3,4,5
        lambdaQueryWrapper.orderByAsc(Cylinder::getSort);
        Page<Cylinder> cylinderPage = cylinderDao.selectPage(page, lambdaQueryWrapper);
        return Result.success(cylinderPage);
    }

    /**
     * 气象色谱仪-详情-曲线图
     *
     * @param
     * @return
     */
    @Override
    public Result atmosphereExportGraph(String id) {
        LambdaQueryWrapper<Cylinder> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Cylinder::getResId, id);
        lambdaQueryWrapper.orderByAsc(Cylinder::getSort);
        List<Cylinder> cylinders = cylinderDao.selectList(lambdaQueryWrapper);
        return Result.success(cylinders);
    }

    /**
     * 气象色谱仪-导出数据
     *
     * @param
     * @return
     */
    @Override
    public Result atmosphereImport(String id, HttpServletResponse response) {
        LambdaQueryWrapper<Cylinder> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Cylinder::getResId, id);
        lambdaQueryWrapper.orderByAsc(Cylinder::getDateTime);
        List<Cylinder> cylinders = cylinderDao.selectList(lambdaQueryWrapper);


        //查询气象色谱仪的设备地址和实验次序号
//        Configuration configuration = configurationDao.selectById(id);
//        String address = configuration.getAddress();
//        String experiment = configuration.getExperiment();


        // 创建一个97年-2003.xls工作簿(一个工作簿中可以创建多个工作表sheet)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //使用工作簿创建一个工作表
        HSSFSheet sheet = hssfWorkbook.createSheet("无量纲数据");
        HSSFRow row = sheet.createRow(0);//获取第一行
        //设置excel表头
        String[] headers = new String[]{"数据点时间", "无量纲(%)"};
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);//当前行的第几列
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);//获取头信息，这个对象会自动给你判空
            cell.setCellValue(text);//设置值
        }
        for (int i = 0; i < cylinders.size(); i++) {
            //创建当前行
            HSSFRow row1 = sheet.createRow(i + 1);//创建第一行(从第二行开始)
            //设置商品名称
            HSSFCell cell1 = row1.createCell(0);//当前行的第1列
            if (StringUtils.hasText(cylinders.get(i).getDateTime())) {
                cell1.setCellValue(cylinders.get(i).getDateTime());
            }
            //设置商品重量
            HSSFCell cell2 = row1.createCell(1);//当前行的第2列
            if (Objects.nonNull(cylinders.get(i).getCylinderData())) {
                cell2.setCellValue(cylinders.get(i).getCylinderData());
            }
        }
        String filename = System.currentTimeMillis() + IdUtil.simpleUUID() + ".xls";
//        String filename = "气象色谱仪_"+address+"_"+experiment+"次实验.xls";
//        filename = filename.replaceAll(":","");
        OutputStream outputStream = null;
        try {
            File exportDirFile = new File(exportDir);
            log.info("exportDir={}", exportDir);
            log.info("exportDirFile={}", exportDirFile.getPath());
            if (!exportDirFile.exists()) {
                boolean mkdirs = exportDirFile.mkdirs();
                if (mkdirs) {
                    log.info("创建文件夹{}成功", exportDirFile);
                } else {
                    log.error("创建文件夹失败");
                }
            }
            String fileUrl = exportDirFile.getPath() + File.separator + filename;
            log.info("fileUrl={}", fileUrl);
            outputStream = Files.newOutputStream(Paths.get(fileUrl));
            hssfWorkbook.write(outputStream);
            //将所有的windows \ 换成 / 以免Linux不支持
            return Result.success(fileUrl.replace("\\", "/"));
        } catch (Exception exception) {
            exception.printStackTrace();
            return Result.error("IO error");
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();//关闭流
                }
                hssfWorkbook.close();//关闭流
            } catch (Exception exception) {
                exception.printStackTrace();
            }
        }
    }

    @Override
    public Result ceegetwarning() {
        Set<String> strings = GlobalData.DEVICE_IS_RUN.keySet();
        List<JSONObject> list = new ArrayList<>();
        for (String s : strings) {//s为设备号+地址值 v2.0之后应该是只有地址值
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("address", s.substring(s.length() - 1));//地址,,这里取得是最后一位
            jsonObject.put("deviceNo", "占位符");//设备号 这里取得是除最后一位的所有
            jsonObject.put("is_run", GlobalData.DEVICE_IS_RUN.get(s));
            JSONObject jsonObject1 = GlobalData.RUN_DEVICE_DATA.get(s);
            String string1 = jsonObject1.getString("status");//0记录中.1记录结束,3未开始记录
            jsonObject.put("status", string1);
            jsonObject.put("deviceName", getDeviceName(s));
            list.add(jsonObject);
        }
        QueryWrapper<Warning> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("is_read", 0);
        List<Warning> warnings = warningMapper.selectList(queryWrapper);
        List<JSONObject> list1 = new ArrayList<>();
        warnings.forEach(warning -> {
            String jsonString = JSON.toJSONString(warning);
            JSONObject jsonObject = JSON.parseObject(jsonString);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String formattedDate = sdf.format(warning.getCreateTime());

            jsonObject.put("createtime", formattedDate);


        });
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("warnings", warnings);
        jsonObject.put("device", list);
        return Result.success(jsonObject);
    }

    @Override
    public Result isread(String id) {
        Warning warning = warningMapper.selectById(id);
        warning.setIs_read("1");
        warningMapper.updateById(warning);
        return Result.success();
    }

    @Override
    public Boolean needSend() {
        QueryWrapper<Warning> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("is_read", 0);
        List<Warning> warnings = warningMapper.selectList(queryWrapper);
        if (warnings.isEmpty()) {
            return false;
        } else {
            return true;
        }
    }

    @Override
    public Result impost(MultipartFile multipartFile) {
        ExcelUtil excelUtil = new ExcelUtil(multipartFile);
        try {
            //获取文件流
            Workbook workbook = excelUtil.getWorkbook();
            log.info("成功将上传的文件转为excel工作簿");
            //获取有效sheet
            List<Integer> effectiveNum = excelUtil.getEffectiveNum();
            for (Integer sheetNum : effectiveNum) {
                //获取当前页的sheet
                Sheet sheet = workbook.getSheetAt(sheetNum);
                //获得当前sheet的第一个有数据的行号开始行号
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的最后一个有数据的行号结束行号
                int lastRowNum = sheet.getLastRowNum();
                //这里跳过第0行，因为第0行是标题，所以是firstRowNum+1开始获取
                for (int nowRowNum = firstRowNum + 1; nowRowNum <= lastRowNum; nowRowNum++) {
                    Row row = sheet.getRow(nowRowNum);
                    if (Objects.isNull(row)) {
                        continue;
                    }
                    //此数组用来存储一行中每一个单元格中的数据，数组长度在初始化时，就是当前行不为空的列的个数
                    for (int cellNum = 0; cellNum < 2; cellNum++) {
                        //获取当前行中的每一个单元格
                        Cell cell = row.getCell(cellNum);
                        //设置单元格类型
                        cell.setCellType(CellType.STRING);
                        //获取单元格中的值
                        String stringCellValue = cell.getStringCellValue();
                    }
                }
            }
            //关闭流
            workbook.close();
        } catch (IOException e) {
            log.error("上传的文件不是excel,不能转换");
            e.printStackTrace();
        } catch (BusinessException e) {
            log.error("工作流获取失败");
            e.printStackTrace();
        }
        return Result.success();
    }

    @Override
    public Result predictionCreate(JSONObject jsonObject) {
        List<JSONObject> list = new ArrayList<>();
        List<String> infos = new ArrayList<>();//比对中信息

        JSONObject data = new JSONObject();
        data.put("name","类比区块");
        data.put("infos",infos);
        list.add(data);
        return Result.success(list);
    }

    /**
     * 获取设备号和地址在设备名称资源文件中的名称
     *
     * @param device
     * @return
     */
    private String getDeviceName(String device) {
        Device device1 = deviceMapper.selectById(device);
        if (Optional.ofNullable(device1).isPresent()) {
            return device1.getDeviceName();
        } else {
            return " ";
        }

    }

    public boolean strData(String strData) {
        Pattern pattern = Pattern.compile("^(100(\\.0{1,3})?)|([0-9]{1,2}(\\.[0-9]{1,3})?)$");
        Matcher matcher = pattern.matcher(strData);
        return matcher.matches();
    }
}
